package kr.hkit.android_caloriecheck.dao;

import java.util.ArrayList;

import kr.hkit.android_caloriecheck.DBHelper;
import kr.hkit.android_caloriecheck.dto.MyItem;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class DAO {
	private DBHelper helper;
	private SQLiteDatabase db;

	private DAO(Context context) {
		helper = new DBHelper(context);
		db = helper.getWritableDatabase();
	}

	public static DAO open(Context context) {
		return new DAO(context);
	}

	public void close() {
		helper.close();
	}

	public boolean foodInsert(String name, int kcal) {
		String sql = "insert into food values(" + "null, " + "'" + name + "',"
				+ " '" + kcal + "');";
		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	public ArrayList<MyItem> foodSelectAll() {
		ArrayList<MyItem> arList = new ArrayList<>();
		MyItem food;

		String sql = "select food, foodkcal from food order by food;";
		Cursor cursor = db.rawQuery(sql, null);

		while (cursor.moveToNext()) {
			String name = cursor.getString(0);
			int kcal = cursor.getInt(1);
			food = new MyItem(name, kcal);
			arList.add(food);
		}
		return arList;
	}

	public ArrayList<MyItem> foodSelectByName(String foodname) {
		ArrayList<MyItem> arList = new ArrayList<>();
		MyItem food;

		String sql = "select food, foodkcal from food where food like'%"
				+ foodname + "%';";
		Cursor cursor = db.rawQuery(sql, null);

		while (cursor.moveToNext()) {
			String name = cursor.getString(0);
			int kcal = cursor.getInt(1);
			food = new MyItem(name, kcal);
			arList.add(food);
		}
		return arList;
	}
	
	public ArrayList<MyItem> foodSelectByName2(String foodname) {
		ArrayList<MyItem> arList = new ArrayList<>();
		MyItem food;

		String sql = "select food, foodkcal from food where food='"
				+ foodname + "';";
		Cursor cursor = db.rawQuery(sql, null);

		while (cursor.moveToNext()) {
			String name = cursor.getString(0);
			int kcal = cursor.getInt(1);
			food = new MyItem(name, kcal);
			arList.add(food);
		}
		return arList;
	}

	public boolean exerciseInsert(String name, int kcal) {
		String sql = "insert into exercise values(" + "null, " + "'" + name
				+ "'," + " '" + kcal + "');";
		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	public ArrayList<MyItem> exerciseSelectAll() {
		ArrayList<MyItem> arList = new ArrayList<>();
		MyItem exercise;

		String sql = "select exercise, exercisekcal from exercise order by exercise;";
		Cursor cursor = db.rawQuery(sql, null);

		while (cursor.moveToNext()) {
			String name = cursor.getString(0);
			int kcal = cursor.getInt(1);
			exercise = new MyItem(name, kcal);
			arList.add(exercise);
		}
		return arList;
	}

	public ArrayList<MyItem> exerciseSelectByName(String exercisename) {
		ArrayList<MyItem> arList = new ArrayList<>();
		MyItem exercise;

		String sql = "select exercise, exercisekcal from exercise where exercise like'%"
				+ exercisename + "%';";
		Cursor cursor = db.rawQuery(sql, null);

		while (cursor.moveToNext()) {
			String name = cursor.getString(0);
			int kcal = cursor.getInt(1);
			exercise = new MyItem(name, kcal);
			arList.add(exercise);
		}
		return arList;
	}

	public boolean foodListInsert(String name, int kcal, int year, int month,
			int day) {
		String sql = "insert into foodList values(" + "null, " + "'" + name
				+ "', " + "'" + kcal + "', " + "'" + year + "', " + "'" + month
				+ "', " + "'" + day + "'" + ");";

		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	public boolean exerciseListInsert(String name, int kcal, int year,
			int month, int day) {
		String sql = "insert into exerciseList values(" + "null, " + "'" + name
				+ "', " + "'" + kcal + "', " + "'" + year + "', " + "'" + month
				+ "', " + "'" + day + "'" + ");";

		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	public ArrayList<MyItem> foodListSelectByDate(int year, int month, int day) {
		ArrayList<MyItem> arList = new ArrayList<>();
		MyItem food;

		String sql = "select food, foodkcal from foodList where year='" + year
				+ "' and month='" + month + "' and day='" + day + "';";

		Cursor cursor = db.rawQuery(sql, null);

		while (cursor.moveToNext()) {
			String name = cursor.getString(0);
			int kcal = cursor.getInt(1);
			food = new MyItem(name, kcal);
			arList.add(food);
		}

		return arList;
	}

	public ArrayList<MyItem> exerciseListSelectByDate(int year, int month,
			int day) {
		ArrayList<MyItem> arList = new ArrayList<>();
		MyItem food;

		String sql = "select exercise, exercisekcal from exerciseList where year='"
				+ year + "' and month='" + month + "' and day='" + day + "';";

		Cursor cursor = db.rawQuery(sql, null);

		while (cursor.moveToNext()) {
			String name = cursor.getString(0);
			int kcal = cursor.getInt(1);
			food = new MyItem(name, kcal);
			arList.add(food);
		}

		return arList;
	}

	public int foodkcalSumSelectByDate(int year, int month, int day) {
		int sum = 0;

		String sql = "Select sum(foodkcal) from foodList where year='" + year
				+ "' and month='" + month + "' and day='" + day + "';";

		Cursor cursor = db.rawQuery(sql, null);
		if (cursor != null) {
			cursor.moveToFirst();
			sum = cursor.getInt(0);
		}

		return sum;
	}

	public int exercisekcalSumSelectByDate(int year, int month, int day) {
		int sum = 0;

		String sql = "Select sum(exercisekcal) from exerciseList where year='"
				+ year + "' and month='" + month + "' and day='" + day + "';";

		Cursor cursor = db.rawQuery(sql, null);
		if (cursor != null) {
			cursor.moveToFirst();
			sum = cursor.getInt(0);
		}

		return sum;
	}
	
	public boolean foodListDeleteByName(String name, int year, int month, int day) {
		String sql = "delete from foodList where food='" + name +"' and year='"
				+ year + "' and month='" + month + "' and day='" + day + "';";
		
		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			return false;
		}
		return true;
	}
	
	public boolean exerciseListDeleteByName(String name, int year, int month, int day) {
		String sql = "delete from exerciseList where exercise='" + name + "' and year='"
				+ year + "' and month='" + month + "' and day='" + day + "';";

		try {
			db.execSQL(sql);
		} catch (SQLException e) {
			return false;
		}
		return true;
	}
}
